package com.wangsir.dao;

import com.wangsir.entity.Clazz;
import com.wangsir.entity.Student;
import com.wangsir.util.JdbcHelper;
import com.wangsir.util.PagerVO;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ClazzDao {

    //查询表中的班级的总数量
    public int count(String whereSql){
        if(whereSql == null){
            whereSql = "";
        }
        JdbcHelper jdbcHelper = new JdbcHelper();
        ResultSet resultSet = jdbcHelper.executeQuery("select count(1) from tb_clazz" + whereSql);
        try {
            //指针下移
            resultSet.next();
            //返回第一个索引列表
            return resultSet.getInt(1);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcHelper.closeDB();
        }
        return 0;
    }
    //重载上面的方法(无参数)
    public int count(){
        return count("");
    }


    //每个班级学生数量的统计，一个班级班级中可能有多个学生，把班级的主键放在学生的表中作为外键进行查询；
    public List<Clazz> statistics(){
        String sql = "select  c.clazzno,c.name,count(1) as stuCount \n" +
                "FROM tb_clazz c,tb_student s\n" +
                "where c.clazzno = s.clazzno\n" +
                "GROUP BY c.clazzno,c.name ORDER BY count(1) DESC";
        JdbcHelper jdbcHelper = new JdbcHelper();
        ResultSet resultSet = jdbcHelper.executeQuery(sql);
        try{
            List<Clazz> list = new ArrayList<>();
            while(resultSet.next()){
                Clazz clazz = new Clazz();
                clazz.setClazzNo(resultSet.getString("clazzno"));
                clazz.setName(resultSet.getString("name"));
                clazz.setStuCount(resultSet.getInt("stuCount"));
                list.add(clazz);
            }
            return list;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            jdbcHelper.closeDB();
        }
        return null;
    }

    //分页
    //返回前端告诉用户有多少条记录，和当前页面是第几页
    public PagerVO<Clazz> page(int currentPage, int pageSize, String whereSql){
        PagerVO<Clazz> clazzPagerVO = new PagerVO<>();
        //设置当前第几页
        clazzPagerVO.setCurrentPage(currentPage);
        //设置一页有多少班级
        clazzPagerVO.setPageSize(pageSize);
        JdbcHelper jdbcHelper = new JdbcHelper();
        ResultSet resultSet = jdbcHelper.executeQuery("select count(1) from tb_clazz" + whereSql);
        try{
            resultSet.next();
            int total = resultSet.getInt(1);
            //设置总班级数量，数据库中的总行数
            clazzPagerVO.setTotalSize(total);
            //select * from tb_student where ...limit 10 , 10
            resultSet = jdbcHelper.executeQuery("select * from tb_clazz" +
                    //limit后边一定是需要写一个空格的否则不符合语法
                    whereSql + "limit " + ((currentPage - 1) * pageSize)+ "," + pageSize);
            List<Clazz> list = new ArrayList<>();
            while(resultSet.next()){
                //调用下面写的静态接口返回学生对象，增加代码的复用性
                Clazz clazz = new Clazz();
                clazz.setClazzNo(resultSet.getString("clazzno"));
                clazz.setName(resultSet.getString("name"));
                list.add(clazz);
            }
            clazzPagerVO.setList(list);
            return clazzPagerVO;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            jdbcHelper.closeDB();
        }
        return null;
    }

    //插入班级
    public int insertClazz(Clazz clazz){
        JdbcHelper jdbcHelper = new JdbcHelper();
        int result = jdbcHelper.executeUpdate("insert into tb_clazz values(?,?)",
                clazz.getClazzNo(), clazz.getName());
        jdbcHelper.closeDB();
        return result;
    }
    //修改班级信息
    public int updateClazz(Clazz clazz){
        JdbcHelper jdbcHelper = new JdbcHelper();
        //为空的属性不做更新
        int result = 0;
        //根据班级表的主键进行修改
        String sql = "update tb_clazz set name = ? where clazzno = ?";
        result = jdbcHelper.executeUpdate(sql, clazz.getName(), clazz.getClazzNo());
        jdbcHelper.closeDB();
        return result;
    }
    //根据班级的主键查询班级的具体信息
    public Clazz getByClazznNO(String clazzno){
        JdbcHelper jdbcHelper = new JdbcHelper();
        ResultSet resultSet = jdbcHelper.executeQuery("select * from tb_clazz where clazzno = ?", clazzno);
        Clazz clazz = new Clazz();
        try{
            while(resultSet.next()){
                clazz.setClazzNo(resultSet.getString("clazzno"));
                clazz.setName(resultSet.getString("name"));
            }
            return clazz;
        }catch(Exception e){
            e.printStackTrace();
        }finally {
            jdbcHelper.closeDB();
        }
        return null;
    }





    //删除班级
    public int delete (String clazzno){
        JdbcHelper jdbcHelper = new JdbcHelper();
        int count = jdbcHelper.executeUpdate("delete from tb_clazz where clazzno = ?",clazzno);
        jdbcHelper.closeDB();
        return count;
    }


    public List<Clazz> listAll() {
        JdbcHelper jdbcHelper = new JdbcHelper();
        ResultSet resultSet;
        try{
            resultSet = jdbcHelper.executeQuery("select * from tb_clazz");
            List<Clazz> list = new ArrayList<>();
            while(resultSet.next()) {
                Clazz clazz = new Clazz();
                clazz.setClazzNo(resultSet.getString("clazzno"));
                clazz.setName(resultSet.getString("name"));
                list.add(clazz);
            }
            return list;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            jdbcHelper.closeDB();
        }
        return null;
    }
}
